package com.db.dbdocument.generateDB.query.mysql;

import com.db.dbdocument.generateDB.common.DataSoreceConnect;
import com.db.dbdocument.generateDB.common.JdbcConnect;
import com.db.dbdocument.generateDB.util.StrUtils;
import com.db.dbdocument.generateDB.vo.DbTableInfoVo;
import com.db.dbdocument.generateDB.vo.DbTableStructureInfoVo;
import org.springframework.util.ObjectUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MysqlDataBaseQuery {


    public static List<DbTableInfoVo> getTableInfo(List<Map<String,Object>> dbTableList){

        List<DbTableInfoVo> dbTableInfoVoList = new ArrayList<>();
        for (Map<String, Object> dbTable : dbTableList){

            DbTableInfoVo dbTableInfoVo = new DbTableInfoVo();
            dbTableInfoVo.setTableName(dbTable.getOrDefault("TABLE_NAME","").toString());
            dbTableInfoVo.setEngine(StrUtils.valueOf(dbTable.getOrDefault("ENGINE","")));
            dbTableInfoVo.setTableComment(StrUtils.valueOf(dbTable.getOrDefault("REMARKS","")));
            dbTableInfoVo.setTableCollation(StrUtils.valueOf(dbTable.getOrDefault("TABLE_COLLATION","")));
            dbTableInfoVo.setTableCollation(StrUtils.valueOf(dbTable.getOrDefault("TABLE_COLLATION","")));
            dbTableInfoVo.setCreateTime(StrUtils.valueOf(dbTable.getOrDefault("CREATE_TIME","")).replace("T",""));

            dbTableInfoVoList.add(dbTableInfoVo);
        }

        return dbTableInfoVoList;
    }

    public static List<DbTableStructureInfoVo> getTableStructureInfo(Connection connection) throws SQLException {

        //获取所有表结构
        //查询条件 #table_name = '表名' table_schema = (数据库名)
        String tableStructureSql = "select column_name, \n" +
                "\ttable_name,\n" +
                "   data_type, \n" +
                "   column_comment, \n" +
                "   column_key , \n" +
                "   is_nullable ,\n" +
                "   column_type ,\n" +
                "   column_default ,\n"+
                "\t table_schema \n" +
                "from information_schema.columns\n" +
                "where \n" +
                "   table_schema = (select database()) \n" +
                "order by ordinal_position;";


        PreparedStatement preparedStatement = connection.prepareStatement(tableStructureSql);
        ResultSet resultSet = preparedStatement.executeQuery();


        List<Map<String, Object>> dbTableStructureList  = DataSoreceConnect.rsConvertListMap(resultSet);

        List<DbTableStructureInfoVo> dbTableStructureVoList = new ArrayList<>();
        for (Map<String,Object>  dbTableStructure:dbTableStructureList){


            DbTableStructureInfoVo dbTableStructureVo = new DbTableStructureInfoVo();
            dbTableStructureVo.setTableSchema(dbTableStructure.getOrDefault("TABLE_SCHEMA","").toString());
            dbTableStructureVo.setTableName(dbTableStructure.getOrDefault("TABLE_NAME","").toString());
            dbTableStructureVo.setColumnName(dbTableStructure.getOrDefault("COLUMN_NAME","").toString());
            dbTableStructureVo.setDataType(dbTableStructure.getOrDefault("DATA_TYPE","").toString());
            dbTableStructureVo.setColumnComment(StrUtils.valueOf(dbTableStructure.getOrDefault("COLUMN_COMMENT","")));
            dbTableStructureVo.setColumnDefault(StrUtils.valueOf(dbTableStructure.getOrDefault("COLUMN_DEFAULT","")));
            dbTableStructureVo.setColumnKey(StrUtils.valueOf(dbTableStructure.getOrDefault("COLUMN_KEY","")));
            dbTableStructureVo.setIsNullable(dbTableStructure.getOrDefault("IS_NULLABLE","").toString());
            dbTableStructureVo.setColumnType(dbTableStructure.getOrDefault("COLUMN_TYPE","").toString());

            dbTableStructureVoList.add(dbTableStructureVo);
        }


        DataSoreceConnect.close(resultSet);


        return dbTableStructureVoList;

    }

}
